import os
import re
import warnings
from datetime import datetime

import openpyxl as xl
import pandas as pd

from openpyxl.styles import Side, Border, Font, Alignment
from openpyxl.utils import get_column_letter
from openpyxl.utils import column_index_from_string as cis

from mathtools import myround

warnings.filterwarnings('ignore')
# 显示所有列
pd.set_option('display.max_columns', None)
# 显示所有行
pd.set_option('display.max_rows', None)
# 不换行
pd.set_option('expand_frame_repr', False)

side = Side(style="thin", color="000000")
border = Border(left=side, right=side, top=side, bottom=side)
font = Font(
    name="宋体",  # 字体
    size=11,  # 字体大小
    color="000000",  # 字体颜色，用16进制rgb表示
    bold=False,  # 是否加粗，True/False
    italic=False,  # 是否斜体，True/False
    strike=None,  # 是否使用删除线，True/False
    underline=None,  # 下划线, 可选'singleAccounting', 'double', 'single', 'doubleAccounting'
)
alignment = Alignment(
    horizontal='center',  # 水平对齐模式
    vertical='center',  # 垂直对齐模式
    text_rotation=None,  # 旋转角度
    wrap_text=True  # 是否自动换行
)
核对df = pd.DataFrame(
    columns=["项目名称", "底表差额", "结算单体现差额", "底表事务金额", "底表业务金额", "结算单事务金额",
             "结算单业务金额", "底事务-结事务", "底业务-结业务", "（底事务+业务）-（结事务+业务）", "数据核对"]
)
is_difference_within_limit = True


# 遍历文件夹， 返回所有文件名带绝对地址
def allFilePath(file):
    """
    遍历文件夹， 返回所有文件名带绝对地址
    :param file: 路径
    :return: 文件和路径 list
    """
    excel_list = []
    for root, dirs, files in os.walk(file):
        # root 表示当前正在访问的文件夹路径
        # dirs 表示该文件夹下的子目录名list
        # files 表示该文件夹下的文件list
        # 遍历文件
        for f in files:
            if f.endswith(".xlsx") or f.endswith(".xls"):
                excel_list.append(os.path.join(root, f))
    # 遍历所有的文件夹
    # for d in dirs:
    # print(os.path.join(root, d))
    return excel_list


# 写数
def write_data(sheet, data, price, totalPrice, withheld_money, grant_money, special_money, pname, titlestr):
    """  只针对 事务/业务 结算清单
    写数
    data = {
        '工程': [
            ['定额工量', 500, ''],
            ['a项目', 194, ''],
            ['b项目', 242, ''],
            []
        ],
        '财务': [
            ['定额工量', 0, ''],
            ['a项目', 172, ''],
            ['b项目', 272, ''],
            []
        ]
    }
    :param sheet: 当前 sheet
    :param data: 记录需要写入 sheet 的数据
    :param price: 含税的 金额； 21.2 / 31.8
    :param totalPrice: 记录员工总的金额，包含定额与专项的金额
    :param withheld_money: 应该被扣的质保金
    :param pname: 项目名称
    :param titlestr: 事务/业务
    :return: 当前 sheet 的总行数
    """
    print("正在 写数...")
    max_row = 3  # 随时更新当前写数的行数，即当前的最大行数

    sumtime = ""  # 记录最后的合计核算工量公式
    sumprice = ""  # 记录最后的合计核算价格公式

    # 写标题
    sheet.merge_cells(f"A1:D1")
    sheet.merge_cells(f"A2:D2")
    sheet["A1"].value = pname.split("@@@")[0]
    sheet["A2"].value = titlestr[:-2] + "结算单 - " + pname.split("@@@")[-1]
    sheet["A2"].alignment = Alignment(horizontal='right', vertical='center', wrap_text=True)

    sheet["A3"].value = "部门"

    values = ["部门", "辅助项目", "核算工量", "核算价格"]

    # 写数
    for d, projects in data.items():
        for i, project in enumerate(projects):
            max_row += 1

            # 写部门名称
            sheet[f"A{max_row}"].value = d

            if not len(project):
                # 部门列合并
                sheet.merge_cells(f"A{max_row - len(projects) + 1}:A{max_row - 1}")
                # 对应部门下方部门小计那行的数
                sheet.merge_cells(f"A{max_row}:B{max_row}")
                sheet[f"A{max_row}"].value = "部门小计"
                sheet[f"C{max_row}"].value = f"=SUM(C{max_row - len(projects) + 1}:C{max_row - 1})"
                sheet[f"D{max_row}"].value = f"=SUM(D{max_row - len(projects) + 1}:D{max_row - 1})"

                # 记录最后合计公式
                if sumtime:
                    # sumtime += f"+SUM(C{max_row - len(projects) + 1}:C{max_row - 1})"
                    sumtime += f"+C{max_row}"
                else:
                    # sumtime = f"=SUM(C{max_row - len(projects) + 1}:C{max_row - 1})"
                    sumtime = f"=C{max_row}"

                if sumprice:
                    # sumprice += f"+SUM(D{max_row - len(projects) + 1}:D{max_row - 1})"
                    sumprice += f"+D{max_row}"
                else:
                    # sumprice = f"=SUM(D{max_row - len(projects) + 1}:D{max_row - 1})"
                    sumprice = f"=D{max_row}"

            for inx, p in enumerate(project):
                if i == 0:  # 写第三行的后三个表头
                    sheet[f"{get_column_letter(inx + 2)}3"].value = values[inx + 1]
                cell = f"{get_column_letter(inx + 2)}{max_row}"
                if inx == 2 and p == "":  # 写最后一列的公式
                    p = f"=C{max_row}*{price}"
                sheet[cell].value = p

    def _new_data(row, col1="C", col2="D", title="", value1="", value2="", ec=2, flag=False):
        """

        Parameters
        ----------
        row -- 当前行
        col1 -- C列
        col2 -- D列
        title -- 该行前两列合并项
        value1 -- C列值
        value2 -- D列值
        ec -- 前多少列合并
        flag -- 后两列是否合并

        Returns
        -------

        """
        sheet[f"A{row}"].value = title
        sheet.merge_cells(start_row=row, end_row=row, start_column=1, end_column=ec)

        sheet[f"{col1}{row}"].value = value1
        if value1 == "工量":
            sheet[f"{col1}{row}"].value = f"={col2}{row}/{price}"

        sheet[f"{col2}{row}"].value = value2
        if value1 == "业务/事务性合计":
            sheet[f"{col1}{row}"].value = f"={col1}{row - 4}-{col1}{row - 3}+{col1}{row - 2}+{col1}{row - 1}"
            sheet[f"{col2}{row}"].value = f"={col1}{row}*{price}"

        if flag:
            sheet.merge_cells(start_row=row, end_row=row, start_column=3, end_column=4)
            if title != "签字":
                sheet[f"{col1}{row}"].value = f"={col2}{row + 1}/{col2}{row - 1}"
                sheet[f"{col1}{row}"].number_format = "0.00%"

    _new_data(max_row + 1, title=f"{titlestr[:-2]}合计", value1=sumtime, value2=sumprice)
    _new_data(max_row + 2, title="本月预扣质量保证金", value1="工量", value2=withheld_money)
    _new_data(max_row + 3, title="本月发放质量保证金", value1="工量", value2=grant_money)
    _new_data(max_row + 4, title="专项奖", value1="工量", value2=special_money)
    _new_data(max_row + 5, title=f"{titlestr[:-4]}合计", value1="业务/事务性合计")
    _new_data(max_row + 6, title="考核合计", value1="工量", flag=True)
    _new_data(max_row + 7, title=f"考核后{titlestr[:2]}结算额", value1="工量", value2=totalPrice)
    _new_data(max_row + 8, title="签字", flag=True)
    return max_row + 8


# 设置格式
def set_sheet_style(sheet, max_row, col_li=None):
    """
    :param sheet: sheet名
    :param max_row: 当前 sheet 的总行数
    :param col_li: 当前 sheet 的总列["A", "B", "C", "D"]
    :return:
    """
    min_length = 20
    if col_li is None:
        col_li = ["A", "B", "C", "D"]
    if len(col_li) == 5:  # 专项奖励
        min_length = 20
    if len(col_li) == 12:  # 质量保证金
        min_length = 16
    print("正在 设置格式...")

    # 设置格式
    def _style(cell):
        sheet[cell].font = font
        sheet[cell].border = border
        # print(cell)
        if cell != "A2":
            sheet[cell].alignment = alignment

    # 根据标题名称的总长度调整列宽
    # 在对比单元格文本时需要将中文字符识别为 1.7 个长度，英文字符识别为 1 个，这里只需要将文本长度直接加上中文字符数量即可
    # re.findall('([\u4e00-\u9fa5])', sheet["A1"].value) 能够识别大部分中文字符
    cell_len = 0.9 * len(re.findall('([\u4e00-\u9fa5])', str(sheet["A1"].value))) + len(str(sheet["A1"].value))

    # print((cell_len / len(col_li)) + 4)
    for col in col_li:
        sheet.column_dimensions[col].width = (cell_len / len(col_li)) + 4 if (cell_len / len(
            col_li)) + 4 > min_length else min_length  # 设置列宽 最小列宽为 20
        for row in range(1, max_row + 1):
            # 设置字体、边框格式
            _style(f"{col}{row}")
            # 设置行高
            if col == "A":  # 只设置一次就行
                sheet.row_dimensions[row].height = 45 if row == 1 else 30  # 设置行高
    if len(col_li) == 12:  # 质量保证金行高
        sheet.row_dimensions[3].height = 43.5


def sheet_create(sheet, pname, second_name, values, rowoffset, coloffset):
    """
        sheet:sheet对象
        pname:结算单位后填的名称
        values:要填的数值列表
        second_name:第二行日期前面跟着的标识
        rowoffset:前N行不需打入值
        coloffset:前M列不需打入值
    """
    sheet["A1"].value = pname.split("@@@")[0]
    sheet["A2"].value = second_name + " - " + pname.split("@@@")[-1]

    sheet.merge_cells(f"A1:{get_column_letter(len(values[0]))}1")
    sheet.merge_cells(f"A2:{get_column_letter(len(values[0]))}2")
    # 根据values长度推算有多少行，设置行高
    for i in range(len(values) + 3):
        sheet.row_dimensions[i].height = 56 if i in [1, 2] else 30

    # 循环数据区域打入值
    for row in range(len(values) + 1):
        for col in range(len(values[0])):
            if row < len(values):
                sheet.cell(row=row + rowoffset, column=col + coloffset, value=values[row][col])
            sheet[f"{get_column_letter(col + 1)}{row + 2}"].alignment = Alignment(horizontal='center',
                                                                                  vertical='center')
            sheet[f"{get_column_letter(col + 1)}{row + 2}"].border = border

    sheet["A1"].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    sheet["A2"].alignment = Alignment(horizontal='right', vertical='center', wrap_text=True)


def create_整体结算(wb, shiwumoney, yewuwumoney, pname):
    """
    整体结算金额的 sheet
    :param wb: 03 审核 workbook
    :param shiwumoney: 事务结算金额（含税）  包括 定额服务
    :param yewuwumoney: 业务结算金额（含税）
    :param pname: 项目名称
    :return:
    """
    values = [
        ["项目", "业务类型", "结算金额（含税）"],
        ["事务结算金额", "事务服务", shiwumoney],
        ["业务结算金额", "业务服务", yewuwumoney],
        ["合计", "合计", "=C4+C5"],
        ["考核签字", "", ""]
    ]
    col_li = ["A", "B", "C"]  # 指定列设置列宽
    merge_cell_values = []  # 存放合并指定单元格

    print("生成-->>> 整体结算金额 sheet页")
    ws = wb.create_sheet("项目总结算单")

    sheet_create(ws, pname, "项目总结算单", values, rowoffset=3, coloffset=1)

    # 合并单元格
    for i in range(1, 3):
        if i == 2:  # 最后一行
            merge_cell_values.append([i + 5, i + 5, cis(col_li[1]), cis(col_li[2])])
        else:  # 合计
            merge_cell_values.append([i + 5, i + 5, cis(col_li[0]), cis(col_li[1])])
    for sr, er, sc, ec in merge_cell_values:
        ws.merge_cells(start_row=sr, end_row=er, start_column=sc, end_column=ec)

    set_sheet_style(ws, len(values) + 1, col_li=col_li)


def create_服务清单(wb, df, price, totalPrice, withheld_money, grant_money, special_money, titlestr, pname):
    """
    工作结算单据
    :param wb: 03 审核 workbook
    :param df: 01 加上部门的员工工作内容 df
    :param price: 含税的 金额； 21.2 / 31.8
    :param totalPrice: 记录员工总的金额，包含定额与专项的金额
    :param withheld_money: 应该被扣的质保金
    :param titlestr: 创建的 sheet 名称； 事务 / 业务 工作结算单据
    :param pname: 项目名称
    :return: None
    """
    print(f"生成-->>> {titlestr} sheet页")
    data = {}  # 记录各部门各工作内容占用工量； data.key = 部门名称、 data.value = [[工作内容, 工量, 核算价格-空字符串], ..., []]
    calPrice = myround(int(df["工量"].sum()) * price)  # 根据工量计算的总的合计含税价格
    if calPrice == 0:
        return
    # 获取所有部门
    department = df["部门"].unique()
    # print(department)

    # 组织 data
    for dep in department:
        data[dep] = []
        # 获取所有项目
        items = df.loc[df["部门"] == dep, "辅助项目"].unique()
        if len(items) == 1 and items[0] == "定额":
            if myround(df.loc[(df["部门"] == dep) & (df["辅助项目"] == "定额"), "工量"].sum(), 0) == 0:
                continue
        # 核算工量
        for item in items:
            work_hour = myround(df.loc[(df["部门"] == dep) & (df["辅助项目"] == item), "工量"].sum(), 0)
            # 核算价格
            work_price = ""
            # if "定额" in item or "专项" in item:
            if "定额" in item:
                item = "定额服务"
            # continue
            data[dep].append([item, work_hour, work_price])

        # print(data[dep])
        # # 加上定额工量或专项工作
        # if "定额工量" not in data[dep][0] and "专项工作" not in data[dep][0]:
        # 	data[dep].insert(0, [workname, 0, ""])

        data[dep].append([])  # 为了给 部门小计 行 留位置
    ws = wb.create_sheet(titlestr)
    # 写数
    max_row = write_data(ws, data, price, totalPrice, withheld_money, grant_money, special_money, pname,
                         titlestr)

    # 设置格式
    set_sheet_style(ws, max_row)


def create_专项奖励(wb, df, pname):
    # 获取所有部门
    department = df["部门"].unique()
    depdf = df.groupby(["部门", "人员类别（甲、乙、丙、丁）"])["专项工资"].sum()
    values = [["部门", "事务专项工量", "事务专项金额", "业务专项工量", "业务专项金额"]]
    for dep in department:
        专项num = myround(depdf[dep].sum())
        if 专项num == 0:
            continue
        业务专项金额 = myround(depdf[dep]["乙"]) if "乙" in depdf[dep].index else 0
        事务专项金额 = myround(myround(depdf[dep].sum()) - 业务专项金额)

        values.append([dep, f"=C{len(values) + 3}/21.2", 事务专项金额, f"=E{len(values) + 3}/31.8", 业务专项金额])
    values.append(
        [
            "合计",
            f"=ROUND(SUM(B{4}:B{len(values) + 2}),2)",
            f"=ROUND(SUM(C{4}:C{len(values) + 2}),2)",
            f"=ROUND(SUM(D{4}:D{len(values) + 2}),2)",
            f"=ROUND(SUM(E{4}:E{len(values) + 2}),2)"
        ]
    )
    if len(values) == 2:
        return
    sheet = wb.create_sheet("专项奖励")
    sheet_create(sheet, pname, "专项奖励清单", values, rowoffset=3, coloffset=1)
    set_sheet_style(sheet, len(values) + 1, col_li=["A", "B", "C", "D", "E"])


def create_质量保证金(wb, df, pname):
    zldf = df.groupby(["部门", "人员类别（甲、乙、丙、丁）", ]).sum().loc[:,
           ["质保金累计存入", "质保金累计流出", "应发质保金", "应扣质保金"]]
    # zldf = df.groupby(["部门", "人员类别（甲、乙、丙、丁）",])[["质保金累计存入", "质保金累计流出", "应发质保金", "应扣质保金"]].sum()

    # 获取所有部门
    department = df["部门"].unique()
    # depdf = df.groupby("部门").sum().loc[:, "应发质保金"]

    values = [
        [
            # "部门", "累计至当期应扣质量保证金", "累计至上期已发质量保证金", "当期发放质量保证金", "剩余应发质量保证金"
            "部门",
            "累计至上期应扣质量保证金", "累计至上期已发质量保证金",
            "当期预扣事务质量保证金", "当期预扣事务质量保证金工量", "当期预扣业务质量保证金",
            "当期预扣业务质量保证金工量",
            "当期发放事务质量保证金", "当期发放事务质量保证金工量", "当期发放业务质量保证金",
            "当期发放业务质量保证金工量",
            "剩余应发质量保证金"
        ]
    ]
    for i, dep in enumerate(department):
        累计应扣num = myround(zldf.loc[dep]["质保金累计存入"].sum()) if dep in zldf.index.get_level_values(
            0) else 0  # 累计至当期应扣质量保证金
        累计流出num = myround(zldf.loc[dep]["质保金累计流出"].sum()) if dep in zldf.index.get_level_values(
            0) else 0  # 累计已发质量保证金
        # 累计已发num = "=B{0}-D{0}-E{0}".format(i + 4)  # 累计至上期已发质量保证金

        当期应扣num = myround(zldf.loc[dep]["应扣质保金"].sum()) if dep in zldf.index.get_level_values(
            0) else 0  # 当期应扣质量保证金
        当期已发num = myround(zldf.loc[dep]["应发质保金"].sum()) if dep in zldf.index.get_level_values(
            0) else 0  # 当期已发质量保证金

        累计上期应扣 = 累计应扣num - 当期应扣num
        累计上期已发 = 累计流出num - 当期已发num  # 累计至上期已发质量保证金

        剩余应发 = 累计应扣num - 累计流出num  # 剩余应发质量保证金
        if 剩余应发 == 0 and 累计上期应扣 == 0 and 累计上期已发 == 0 and 当期已发num == 0:
            continue

        当期预扣业务质量保证金 = myround(zldf.loc[dep].loc["乙"]["应扣质保金"].sum()) if "乙" in zldf.loc[
            dep].index else 0
        当期预扣业务质量保证金工量 = "=F{0}/31.8".format(i + 4)

        当期预扣事务质量保证金 = myround(当期应扣num - 当期预扣业务质量保证金)
        当期预扣事务质量保证金工量 = "=D{0}/21.2".format(i + 4)

        当期发放业务质量保证金 = myround(zldf.loc[dep].loc["乙"]["应发质保金"].sum()) if "乙" in zldf.loc[
            dep].index else 0
        当期发放业务质量保证金工量 = "=J{0}/31.8".format(i + 4)  # 累计至上期已发质量保证金

        当期发放事务质量保证金 = myround(当期已发num - 当期发放业务质量保证金)
        当期发放事务质量保证金工量 = "=H{0}/21.2".format(i + 4)  # 累计至上期已发质量保证金

        values.append(
            [
                dep,
                累计上期应扣, 累计上期已发,
                当期预扣事务质量保证金, 当期预扣事务质量保证金工量, 当期预扣业务质量保证金, 当期预扣业务质量保证金工量,
                当期发放事务质量保证金, 当期发放事务质量保证金工量, 当期发放业务质量保证金, 当期发放业务质量保证金工量,
                剩余应发
            ]
        )
    last_value = []
    for v in range(len(values[0])):
        if v == 0:
            last_value.append("合计")
        else:
            last_value.append(
                f"=ROUND(SUM({get_column_letter(v + 1)}{4}:{get_column_letter(v + 1)}{len(values) + 2}),2)"
            )
    values.append(last_value)
    if len(values) == 2:
        return
    sheet = wb.create_sheet("质量保证金")
    sheet_create(sheet, pname, "质量保证金清单", values, rowoffset=3, coloffset=1)
    set_sheet_style(sheet, len(values) + 1, col_li=["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L"])


def cal03m(pname):
    """
    记录资金往来记录的含税结算总额
    Parameters
    ----------
    pname 项目名称

    Returns
    -------
    total_money 资金往来记录的含税结算总额
    """
    filename = "项目辅助业务外包结算清单-" + pname + ".xlsx"
    wb = None
    ws = None
    ws2 = None
    for root, dirs, files in os.walk("../A结算票据"):
        if filename in files:
            wb = xl.load_workbook(os.path.join(root, filename))
            ws = wb["外包结算单"]
            ws2 = wb["结算表"]
    money_str = ws["I4"].value
    balance_shiwumoney = myround(ws2["K5"].value * ws2["E5"].value)
    balance_yewumoney = myround(ws2["K6"].value * ws2["E6"].value)
    if wb:
        wb.close()
    try:
        money = money_str.split("-")[-1].split("/")[0].split("(")[-1].replace(",2)", "")
        return myround(-float(money)), balance_shiwumoney, balance_yewumoney
    except Exception as e:
        print("*" * 100)
        for _ in range(5):
            print(" ")
        print(f"=={filename} 项目结算单金额解析失败: {e}==")
        for _ in range(5):
            print(" ")
        print("*" * 100)
        return None, None, None


def 结算单book(员工明细df, 工作内容df, pname, file_path):
    """
    生成审核 03 结算单
    :param 员工明细df: 01 员工基本工资明细 df
    :param 工作内容df: 01 员工工作内容 df
    :param pname: 项目名称
    :param file_path: 文件夹路径
    :return:
    """
    pjname = pname[:-8] if pname[:-8].endswith("项目") else pname[:-8] + "项目"
    ymdate = "20" + pname[-7:][:2] + " 年 " + pname[-7:][2:4] + " 月"
    project_name = pjname + "@@@" + ymdate

    员工明细df.fillna(value=0, inplace=True)
    # 创建 workbook
    wb = xl.Workbook()

    # 获取事务结算金额和业务结算金额
    定额money = myround(员工明细df["定额服务"].sum())

    ################################################################
    # 应扣
    事务df = 员工明细df.loc[~员工明细df["人员类别（甲、乙、丙、丁）"].str.contains('乙'), :]
    shiwutotalmoney = myround(事务df["应发合计"].sum())
    业务df = 员工明细df.loc[员工明细df["人员类别（甲、乙、丙、丁）"].str.contains('乙'), :]
    yewutotalmoney = myround(业务df["应发合计"].sum())

    # 新版 -- 乙 业务；丙丁 事务
    事务应扣 = myround(事务df["应扣质保金"].sum())
    业务应扣 = myround(业务df["应扣质保金"].sum())

    事务发放 = myround(事务df["应发质保金"].sum())
    业务发放 = myround(业务df["应发质保金"].sum())

    事务专项 = myround(事务df["专项工资"].sum())
    业务专项 = myround(业务df["专项工资"].sum())
    ################################################################
    diff_money, balance_shiwumoney, balance_yewumoney = cal03m(pname)

    # 整体结算金额单据
    create_整体结算(wb, balance_shiwumoney, balance_yewumoney, project_name)  # 取结算单 事务 和 业务 金额
    inx = len(核对df)
    核对df.loc[inx, "项目名称"] = pname
    核对df.loc[inx, "底表差额"] = 差额money
    核对df.loc[inx, "底表事务金额"] = shiwutotalmoney
    核对df.loc[inx, "底表业务金额"] = yewutotalmoney

    核对df.loc[inx, "结算单体现差额"] = diff_money
    核对df.loc[inx, "结算单事务金额"] = balance_shiwumoney
    核对df.loc[inx, "结算单业务金额"] = balance_yewumoney
    核对df.loc[inx, "底事务-结事务"] = shiwutotalmoney - balance_shiwumoney
    核对df.loc[inx, "底业务-结业务"] = yewutotalmoney - balance_yewumoney
    核对df.loc[inx, "（底事务+业务）-（结事务+业务）"] = (shiwutotalmoney + yewutotalmoney) - (
            balance_shiwumoney + balance_yewumoney)
    if (
            abs(diff_money) > 32 or
            abs(差额money) > 32 or
            myround(shiwutotalmoney) != myround(balance_shiwumoney) or
            myround(yewutotalmoney) != myround(balance_yewumoney) or
            (myround(shiwutotalmoney) + myround(yewutotalmoney)) != (myround(balance_shiwumoney) + myround(
        balance_yewumoney))
    ):
        核对df.loc[inx, "数据核对"] = "×"
        global is_difference_within_limit
        is_difference_within_limit = False
    else:
        核对df.loc[inx, "数据核对"] = "√"

    # 组织数据 -- 拼部门
    df = pd.merge(
        left=工作内容df,
        right=员工明细df[["身份证号", "部门"]],
        on=["身份证号"]
    )

    # df1 = df.loc[(df["辅助项目"] != "定额") & (df["辅助项目"] != "专项"), :]
    # df1 = df.loc[df["辅助项目"] != "专项", :]  # 添上定额

    # 业务工作结算单据
    create_服务清单(wb, df.loc[df["工作类型"] == 0, :], 31.8, yewutotalmoney, 业务应扣, 业务发放, 业务专项,
                    "业务性服务清单", project_name)

    # 事务工作结算单据
    create_服务清单(wb, df.loc[df["工作类型"] == 1, :], 21.2, shiwutotalmoney, 事务应扣, 事务发放, 事务专项,
                    "事务性服务清单", project_name)

    # 专项奖励
    print(f"生成-->>> 专项奖励 sheet页")
    create_专项奖励(wb, 员工明细df.loc[:, ["身份证号", "人员类别（甲、乙、丙、丁）", "部门", "专项工资"]], project_name)

    # 质量保证金
    print(f"生成-->>> 质量保证金 sheet页")
    create_质量保证金(wb, 员工明细df.loc[:,
                          ["身份证号", "人员类别（甲、乙、丙、丁）", "部门", "应发质保金", "应扣质保金", "质保金累计存入",
                           "质保金累计流出"]],
                      project_name)
    if "Sheet" in wb.sheetnames:
        wb.remove(wb["Sheet"])
    wb.save(f"../审计数据/{file_path}/01-{pname}-结算单.xlsx")


def start():
    print("=" * 10, "开始生成项目结算单", "=" * 10)
    fileList = allFilePath(os.path.join("../审计数据"))
    print("共读取文件数：\t%s" % len(fileList))
    for fileName in fileList:
        file_path = fileName.replace("\\", "/").split("/")[-2]
        pname = fileName.replace("\\", "/").split("/")[-1].replace(".xlsx", "")
        if pname.startswith("~") or pname.startswith("0") or os.path.isdir(os.path.join("../审计数据", fileName)):
            continue

        print("-" * 5, f"正在处理\t{pname}\t项目文件", "-" * 5)
        员工明细df = pd.read_excel(fileName, sheet_name=0, converters={"身份证号": str, "人员类别（甲、乙、丙、丁）": str})
        工作内容df = pd.read_excel(fileName, sheet_name=1, converters={"身份证号": str})
        # 创建 workbook 并组织格式
        结算单book(员工明细df, 工作内容df, pname, file_path)
    print("=" * 10, "项目结算单生成完毕", "=" * 10)
    核对df.to_excel("../审计数据/09-核对数据.xlsx", index=False)
    print("=" * 10, "核对数据生成完毕", "=" * 10)
    return is_difference_within_limit  # 正常 > True ； 差过大 > False


if __name__ == '__main__':
    import time

    s = time.time()
    start()
    e = time.time()
    print("花费时间：%.2f 秒" % (e - s))
